import time

import pandas as pd
from tqdm import tqdm

progress_bar = tqdm(total=100, desc=f"正在执行")
time.sleep(0.5)
progress_bar.update(10)

# 读取表格1
coupon_data = pd.read_excel('excel_hwn/核销2.xlsx', dtype={'订单编号': str, '券码': str, '商品ID': str})  # 替换为您的表格1文件路径

# coupon_data = pd.read_excel('excel_hwn/核销2.xlsx', dtype=str)  # 替换为您的表格1文件路径
progress_bar.update(20)
# 读取表格2
sales_data = pd.read_excel('excel_hwn/抖音.xlsx', dtype={'抖音订单编号': str, '卡券编码': str, '商品编码': str})  # 替换为您的表格2文件路径
# sales_data = pd.read_excel('excel_hwn/抖音.xlsx', dtype=str)  # 替换为您的表格2文件路径
progress_bar.update(20)
# 读取表格3
coupon_details = pd.read_excel('excel_hwn/券明细2.xlsx', dtype=str)  # 替换为您的表格1文件路径
coupon_details = coupon_details[['商品ID', '商品编码']]
exclude_data = pd.read_excel('excel_hwn/不参加.xlsx', dtype={'商品编码': str})  # 替换为您的表格3文件路径
# exclude_data = pd.read_excel('excel_hwn/不参加.xlsx', dtype=str)  # 替换为您的表格3文件路径
progress_bar.update(20)

newtable = coupon_data[['订单编号', '商品名称', '金度科技', '供货商承担金额', '门店承担金额', '费用', '商品原价', '券码', '商品ID']].drop_duplicates()

progress_bar.update(10)

# 合并表格2和表格1
merged_data = pd.merge(sales_data, newtable, left_on='卡券编码', right_on='券码', how='left')
# merged_data.groupby('券码')

progress_bar.update(10)
# merged_data['抖音订单编号','','']
# merged_data.to_excel('合并结果.xlsx', index=False)  # 替换为您想要保存的文件路径

# merged_data = merged_data[['抖音订单编号','卡券编码','卡券名称','商品编码','商品名称','分类编码','分类名称','销售数量','销售金额']].copy()
# #
# #
# # # # 不参与活动商品
# exclude_gods = merged_data[merged_data['商品编码'].isin(exclude_data['商品编码'])].copy()
# exclude_gods['门店应承担费用'] = exclude_gods['金度科技'] + exclude_gods['供货商承担金额']+exclude_gods['门店承担金额']
# exclude_gods['金度科技应承担费用'] = 0
# exclude_gods['供货商应承担费用'] = 0
hebing = pd.merge(merged_data, exclude_data, on='商品编码', how='left', indicator='_merge_1').copy().drop_duplicates()
hebing = hebing.astype({'商品ID': str, '商品编码': str})

# has_coupon_details_code = coupon_details['商品编码'].isnull().all()
# merge_cols = ['商品ID', '商品编码'] if has_coupon_details_code else ['商品ID']

hebing = pd.merge(hebing, coupon_details, on=['商品ID', '商品编码'], how='left', indicator='ddwqr').copy().drop_duplicates()

hebing.to_excel('过程123.xlsx', index=False)

hebing['是否参加'] = 1
hebing.loc[hebing['_merge_1'] == 'both', '是否参加'] = 0
# hebing.loc[hebing['ddwqr'] == 'left_only', '是否参加'] = 0
hebing.loc[hebing['商品编码_y'] == '', '是否参加'] = 1
# hebing = hebing.drop(['_merge'],axis=1)
# hebing.loc[hebing['商品编码'].isnull(),'是否参加'] = 1


# hebing.to_excel("middle.xlsx", index=False)
sales_ratio = hebing['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum')

duochengdan = (sales_ratio * hebing['供货商承担金额'] + sales_ratio * hebing['金度科技']) * (1 - hebing['是否参加'])

hebing['供货商实际承担费用'] = sales_ratio * hebing['供货商承担金额'] * hebing['是否参加']
hebing['金度科技实际承担费用'] = sales_ratio * hebing['金度科技'] * hebing['是否参加']
hebing['门店实际承担费用'] = (sales_ratio * hebing['门店承担金额']) + duochengdan

# hebing['供货商实际承担费用'] = hebing.apply(
#                row['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum') * row['供货商承担金额'] + row['销售金额'] /
# #                 hebing.groupby('券码')['销售金额'].transform('sum') * row['金度科技'] + row['销售金额'] / hebing.groupby('券码')[
# #                     '销售金额'].transform('sum') * row['门店承担金额']),axis=1)
#
# # hebing.loc[hebing['是否参加'] == 1, '供货商实际承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
# #     'sum') * merged_data['供货商承担金额']
# # hebing.loc[hebing['是否参加'] == 1, '金度科技实际承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
# #     'sum') * merged_data['金度科技']
# # hebing.loc[hebing['是否参加'] == 1, '门店实际承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
# #     'sum') * merged_data['门店承担金额']
# #
# # hebing.loc[hebing['是否参加'] == 0, '供货商实际承担费用'] = 0
# # hebing.loc[hebing['是否参加'] == 0, '金度科技实际承担费用'] = 0
# # hebing.loc[hebing['是否参加'] == 0, '门店实际承担费用'] = (merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
# #     'sum') * merged_data['门店承担金额']) + (merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform(
# #     'sum') * merged_data['供货商承担金额']) + (merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform('sum') *
# #                                         merged_data['金度科技'])    lambda row: row['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum') * row['供货商承担金额'] if row['是否参加'] == 1 else 0,axis=1)
# hebing['金度科技实际承担费用'] = hebing.apply(
#     lambda row: row['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum') * row['金度科技'] if row['是否参加'] == 1 else 0,axis=1)
# hebing['门店实际承担费用'] = hebing.apply(
#     lambda row: row['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum') * row['门店承担金额'] if row['是否参加'] == 1 else (
#

hebing = hebing.astype({'供货商实际承担费用': float, '金度科技实际承担费用': float, '门店实际承担费用': float, '券码': str, '商品ID': str})
# hebing['金度科技实际承担费用'] = hebing['金度科技实际承担费用'].astype(float)
# hebing['门店实际承担费用'] = hebing['门店实际承担费用'].astype(float)
# hebing['券码'] = hebing['券码'].apply(lambda x: str(int(x)) if pd.notna(x) else '')
# hebing.drop('是否参加', axis=1, inplace=True)
# hebing.drop('_merge', axis=1, inplace=True)
# hebing.drop('商品名称', axis=1, inplace=True)
# hebing.drop('条码', axis=1, inplace=True)
# hebing.drop('单位', axis=1, inplace=True)
# hebing.drop('零售价', axis=1, inplace=True)
# hebing.drop('采购', axis=1, inplace=True)
# hebing.drop('支付信息', axis=1, inplace=True)
# hebing.drop('流水号', axis=1, inplace=True)
# hebing.drop('店名', axis=1, inplace=True)
# hebing.drop('店号', axis=1, inplace=True)
progress_bar.update(5)
# hebing['供货商实际承担费用']= hebing.groupby
# hebing['金度科技实际承担费用']=
# hebing['门店实际承担费用']=
# # # # 参与活动商品
# join_gods = merged_data[~merged_data['商品编码'].isin(exclude_data['商品编码'])].copy()
#
# # 计算供应商应承担费用
# join_gods['供货商应承担费用'] = merged_data['销售金额'] / merged_data.groupby('券码')['销售金额'].transform('sum') * merged_data[
#     '供货商承担金额']
# join_gods['金度科技应承担费用'] = 0
# join_gods['供货商应承担费用'] = 0

# result = pd.concat([exclude_gods, join_gods], axis=0, ignore_index=True)
excelName = '最后测试.xlsx'
hebing.to_excel(f'out/{excelName}', index=False)
progress_bar.update(5)
progress_bar.close()
print(f'表格【{excelName}】计算完毕')
# # 计算门店、今度科技、供应商各自承担的费用
# merged_data['金度科技实际承担'] = merged_data['金度科技']
# merged_data['供货商实际承担'] = merged_data['供货商应承担费用']
# merged_data['门店实际承担'] = merged_data['门店承担金额']
#
# exce2 = merged_data

# # # 保留不参加活动的商品
# merged_data = merged_data[~merged_data['商品编码'].isin(exclude_data['商品编码'])]
# # 计算门店、今度科技、供应商各自承担的费用
# merged_data['金度科技实际承担'] = 0
# merged_data['供货商实际承担'] = 0
# merged_data['门店实际承担'] = merged_data['门店承担金额']+merged_data['金度科技']+merged_data['供货商应承担费用']
#
# exce1 = merged_data
#
#
# result = pd.concat([exce1, exce2], axis=0, ignore_index=True)
#
#
#
# # # 以订单为单位，计算各方承担的总费用
# total_costs = result.groupby('抖音订单编号').agg({
#     '金度科技实际承担': 'sum',
#     '供货商实际承担': 'sum',
#     '门店实际承担': 'sum',
#     '商品编码':'first',
#     '商品名称_x':'first'
# }).reset_index()
# 重命名列，以更好地表示数据
# result.rename(columns={
#     '金度科技实际承担': '总金度科技承担费用',
#     '供应商实际承担': '总供应商承担费用',
#     '门店实际承担': '总门店承担费用',
#     '商品编号': '商品编号明细',
#     '商品名称': '商品名称明细'
# }, inplace=True)
# # 保存结果到新的Excel文件
# total_costs.to_excel('最终结果.xlsx', index=False)  # 替换为您想要保存的文件路径
# #
# print("处理完成，最终结果已保存到 '最终结果.xlsx'")
